
Imports System.Data
Imports System.Data.SqlClient


Namespace Producto
Public Class Catalogo

public Function Listar(CatalogoId as integer) as Entity.Producto.Catalogo

Dim strNameProcedure as string = "up_Pro_Catalogo_obtener"
Dim sqlReader As SqlDataReader = Nothing
Dim sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
dim objEnt as   entity.Producto.Catalogo
Try
            sqlCon.Open()
Dim sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
With sqlCmd
.CommandType = CommandType.StoredProcedure
                .Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.Int)).Value =CatalogoId
            End With
            sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)
If sqlReader.HasRows Then
While sqlReader.Read
 objEnt= new    entity.Producto.Catalogo
With objEnt
		.CatalogoId = sqlReader("CatalogoId").ToString
		.Codigo = sqlReader("Codigo").ToString
		.Nombre = sqlReader("Nombre").ToString
		.Concentracion = sqlReader("Concentracion").ToString
		.FormaID = sqlReader("FormaID").ToString
		.FormaSimpleId = sqlReader("FormaSimpleId").ToString
		.Presentacion = sqlReader("Presentacion").ToString
		.Fracciones = sqlReader("Fracciones").ToString
		.FechaRegistroSanitario = sqlReader("FechaRegistroSanitario").ToString
		.RegistroSanitario = sqlReader("RegistroSanitario").ToString
		.MarcaId = sqlReader("MarcaId").ToString
		.Generico = sqlReader("Generico").ToString
End With
  End While
  End IF
 Catch ex As Exception
Throw New Exception(ex.ToString)
Finally
 sqlReader.Dispose()
  sqlReader.Close()
  sqlReader = Nothing
  sqlCon.Dispose()
 sqlCon.Close()
 sqlCon = Nothing
 End Try
return objEnt

End Function


'***************************



public Function Listar(Opcion as string , _
		CatalogoId as Integer, _
		Codigo as String, _
		Nombre as String, _
		Concentracion as String, _
		FormaID as Integer, _
		FormaSimpleId as Integer, _
		Presentacion as String, _
		Fracciones as Integer, _
		FechaRegistroSanitario as Integer, _
		RegistroSanitario as String, _
		MarcaId as Integer, _
		Generico as Integer) _
as  Entity.Producto.CatalogoCol

Dim strNameProcedure as string = "up_Pro_Catalogo_listar"
dim objCol as   entity.Producto.CatalogoCol
Try
Using conn As New SqlConnection(Configuracion.Connexion.Cnn)
Dim cmd As New SqlCommand(strNameProcedure, conn)
cmd.CommandType = CommandType.StoredProcedure
  cmd.Parameters.Add(New SqlParameter("@opcion", SqlDbType.varchar,50)).Value =Opcion
 cmd.Parameters.Add(New SqlParameter("@CatalogoId",SqlDbType.INT)).Value =CatalogoId
 cmd.Parameters.Add(New SqlParameter("@Codigo",SqlDbType.VARCHAR,50)).Value =Codigo
 cmd.Parameters.Add(New SqlParameter("@Nombre",SqlDbType.VARCHAR,255)).Value =Nombre
 cmd.Parameters.Add(New SqlParameter("@Concentracion",SqlDbType.VARCHAR,255)).Value =Concentracion
 cmd.Parameters.Add(New SqlParameter("@FormaID",SqlDbType.INT)).Value =FormaID
 cmd.Parameters.Add(New SqlParameter("@FormaSimpleId",SqlDbType.INT)).Value =FormaSimpleId
 cmd.Parameters.Add(New SqlParameter("@Presentacion",SqlDbType.VARCHAR,255)).Value =Presentacion
 cmd.Parameters.Add(New SqlParameter("@Fracciones",SqlDbType.INT)).Value =Fracciones
 cmd.Parameters.Add(New SqlParameter("@FechaRegistroSanitario",SqlDbType.BIGINT)).Value =FechaRegistroSanitario
 cmd.Parameters.Add(New SqlParameter("@RegistroSanitario",SqlDbType.VARCHAR,50)).Value =RegistroSanitario
 cmd.Parameters.Add(New SqlParameter("@MarcaId",SqlDbType.INT)).Value =MarcaId
 cmd.Parameters.Add(New SqlParameter("@Generico",SqlDbType.SMALLINT)).Value =Generico
conn.Open()
Using dr As SqlDataReader = cmd.ExecuteReader()
	

 Dim colCount As Integer = dr.FieldCount
 Dim values As Object() = New Object(colCount - 1) {}

 objCol =  New entity.Producto.CatalogoCol
  If dr.HasRows Then
                            While dr.Read()
                                Dim iCatalogoId As Integer = dr.GetOrdinal("CatalogoId")
                                Dim iCodigo As Integer = dr.GetOrdinal("Codigo")
                                Dim iNombre As Integer = dr.GetOrdinal("Nombre")
                                Dim iConcentracion As Integer = dr.GetOrdinal("Concentracion")
                                Dim iFormaID As Integer = dr.GetOrdinal("FormaID")
                                Dim iFormaSimpleId As Integer = dr.GetOrdinal("FormaSimpleId")
                                Dim iPresentacion As Integer = dr.GetOrdinal("Presentacion")
                                Dim iFracciones As Integer = dr.GetOrdinal("Fracciones")
                                Dim iFechaRegistroSanitario As Integer = dr.GetOrdinal("FechaRegistroSanitario")
                                Dim iRegistroSanitario As Integer = dr.GetOrdinal("RegistroSanitario")
                                Dim iMarcaId As Integer = dr.GetOrdinal("MarcaId")
                                Dim iGenerico As Integer = dr.GetOrdinal("Generico")
                                Dim i_usu_Cre As Integer = dr.GetOrdinal("_usu_Cre")
                                Dim i_Fec_cre As Integer = dr.GetOrdinal("_Fec_cre")
                                Dim i_usu_mod As Integer = dr.GetOrdinal("_usu_mod")
                                Dim i_fec_mod As Integer = dr.GetOrdinal("_fec_mod")
                                Dim i_flg_Eli As Integer = dr.GetOrdinal("_flg_Eli")

                                Dim objEnt = New Entity.Producto.Catalogo
                                dr.GetValues(values)

                                objEnt.CatalogoId = values(iCatalogoId)
                                objEnt.Codigo = values(iCodigo)
                                objEnt.Nombre = values(iNombre)
                                objEnt.Concentracion = values(iConcentracion)
                                objEnt.FormaID = values(iFormaID)
                                objEnt.FormaSimpleId = values(iFormaSimpleId)
                                objEnt.Presentacion = values(iPresentacion)
                                objEnt.Fracciones = values(iFracciones)
                                objEnt.FechaRegistroSanitario = values(iFechaRegistroSanitario)
                                objEnt.RegistroSanitario = values(iRegistroSanitario)
                                objEnt.MarcaId = values(iMarcaId)
                                objEnt.Generico = values(iGenerico)
                                objCol.Add(objEnt)
                            End While
 End if
 End Using
 End Using
 Catch ex As Exception
Throw New Exception(ex.ToString)
Finally
 End Try
return objCol

End Function


'***************************


        Public Function Insert(ByVal opcion As String, _
          ByVal CatalogoId As Integer, _
          ByVal Codigo As String, _
          ByVal Nombre As String, _
          ByVal Concentracion As String, _
          ByVal FormaID As Integer, _
          ByVal FormaSimpleId As Integer, _
          ByVal Presentacion As String, _
          ByVal Fracciones As Integer, _
          ByVal FechaRegistroSanitario As Integer, _
          ByVal RegistroSanitario As String, _
          ByVal MarcaId As Integer, _
          ByVal Generico As Integer, _
        ByVal vUsuario As Integer) As Integer

            Dim strNameProcedure As String = "Up_Pro_Catalogo_Insertar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.INT)).Value = CatalogoId
                            .Parameters.Add(New SqlParameter("@Codigo", SqlDbType.VARCHAR, 50)).Value = Codigo
                            .Parameters.Add(New SqlParameter("@Nombre", SqlDbType.VARCHAR, 255)).Value = Nombre
                            .Parameters.Add(New SqlParameter("@Concentracion", SqlDbType.VARCHAR, 255)).Value = Concentracion
                            .Parameters.Add(New SqlParameter("@FormaID", SqlDbType.INT)).Value = FormaID
                            .Parameters.Add(New SqlParameter("@FormaSimpleId", SqlDbType.INT)).Value = FormaSimpleId
                            .Parameters.Add(New SqlParameter("@Presentacion", SqlDbType.VARCHAR, 255)).Value = Presentacion
                            .Parameters.Add(New SqlParameter("@Fracciones", SqlDbType.INT)).Value = Fracciones
                            .Parameters.Add(New SqlParameter("@FechaRegistroSanitario", SqlDbType.BIGINT)).Value = FechaRegistroSanitario
                            .Parameters.Add(New SqlParameter("@RegistroSanitario", SqlDbType.VARCHAR, 50)).Value = RegistroSanitario
                            .Parameters.Add(New SqlParameter("@MarcaId", SqlDbType.INT)).Value = MarcaId
                            .Parameters.Add(New SqlParameter("@Generico", SqlDbType.SMALLINT)).Value = Generico
                            .Parameters.Add(New SqlParameter("@_usuario", SqlDbType.Int)).Value = vUsuario
                            .Parameters.Add(New SqlParameter("@_fecha", SqlDbType.BigInt)).Value = now.tofiletime()
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


        '***************************


        Public Function Update(ByVal opcion As String, _
          ByVal CatalogoId As Integer, _
          ByVal Codigo As String, _
          ByVal Nombre As String, _
          ByVal Concentracion As String, _
          ByVal FormaID As Integer, _
          ByVal FormaSimpleId As Integer, _
          ByVal Presentacion As String, _
          ByVal Fracciones As Integer, _
          ByVal FechaRegistroSanitario As Integer, _
          ByVal RegistroSanitario As String, _
          ByVal MarcaId As Integer, _
          ByVal Generico As Integer, _
        ByVal vUsuario As Integer) As Integer

            Dim strNameProcedure As String = "Up_Pro_Catalogo_Actualizar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.INT)).Value = CatalogoId
                            .Parameters.Add(New SqlParameter("@Codigo", SqlDbType.VARCHAR, 50)).Value = Codigo
                            .Parameters.Add(New SqlParameter("@Nombre", SqlDbType.VARCHAR, 255)).Value = Nombre
                            .Parameters.Add(New SqlParameter("@Concentracion", SqlDbType.VARCHAR, 255)).Value = Concentracion
                            .Parameters.Add(New SqlParameter("@FormaID", SqlDbType.INT)).Value = FormaID
                            .Parameters.Add(New SqlParameter("@FormaSimpleId", SqlDbType.INT)).Value = FormaSimpleId
                            .Parameters.Add(New SqlParameter("@Presentacion", SqlDbType.VARCHAR, 255)).Value = Presentacion
                            .Parameters.Add(New SqlParameter("@Fracciones", SqlDbType.INT)).Value = Fracciones
                            .Parameters.Add(New SqlParameter("@FechaRegistroSanitario", SqlDbType.BIGINT)).Value = FechaRegistroSanitario
                            .Parameters.Add(New SqlParameter("@RegistroSanitario", SqlDbType.VARCHAR, 50)).Value = RegistroSanitario
                            .Parameters.Add(New SqlParameter("@MarcaId", SqlDbType.INT)).Value = MarcaId
                            .Parameters.Add(New SqlParameter("@Generico", SqlDbType.SMALLINT)).Value = Generico
                            .Parameters.Add(New SqlParameter("@_usuario", SqlDbType.Int)).Value = vUsuario
                            .Parameters.Add(New SqlParameter("@_fecha", SqlDbType.BigInt)).Value = now.tofiletime()
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


        '***************************


        Public Shared Function Eliminar(ByVal opcion As String, ByVal CatalogoId As Integer, ByVal vusuario As Integer) As Integer

            Dim strNameProcedure As String = "Up_Pro_Catalogo_Eliminar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.Int)).Value = CatalogoId
                            .Parameters.Add(New SqlParameter("@_usuario", SqlDbType.Int)).Value = vUsuario
                            .Parameters.Add(New SqlParameter("@_fecha", SqlDbType.BigInt)).Value = now.tofiletime()
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


'***************************



End Class



end namespace

